package slevnik;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 * This class represents manager for results of items and categories
 * @author Eduard Krak, Martin Penak
 */
public class ResultManagerImpl implements ResultManager{
    
    private final DataSource pool;
    private static final Logger logger = Logger.getLogger(ResultManagerImpl.class.getName());

    public ResultManagerImpl(DataSource ds) throws SQLException {
        this.pool = ds;
    }

    @Override
    public DataSource getPool() {
        return this.pool;
    }

    @Override
    public List<Item> findItemsByCategory(Category category, String sortingParam, Boolean ascending) throws SlevnikException {
        if (category == null) {
            throw new IllegalArgumentException("Category can't be null");
        }
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = pool.getConnection();
            if (ascending) {
                ps = con.prepareStatement("SELECT * FROM slevnik.items WHERE category_id = ? ORDER BY " + sortingParam + " ASC");
            }
            else {
                ps = con.prepareStatement("SELECT * FROM slevnik.items WHERE category_id = ? ORDER BY " + sortingParam + " DESC");
            }
            ps.setInt(1, category.getId());
            ResultSet rs = ps.executeQuery();
            List<Item> items = new ArrayList<Item>();
            while (rs.next()) {
                Item item = new ItemImpl();
                item.setId(rs.getInt("id"));
                item.setTitle(rs.getString("title"));
                item.setUrl(rs.getString("url"));
                item.setCity(rs.getString("city"));
                item.setFinalPrice(rs.getDouble("final_price"));
                item.setOriginalPrice(rs.getDouble("original_price"));
                item.setDiscount(rs.getDouble("discount"));
                item.setCustomers(rs.getInt("customers"));
                item.setDealStart(rs.getDate("dealstart"));
                item.setDealEnd(rs.getDate("dealend"));
                item.setCategoryId(rs.getInt("category_id"));
                items.add(item);
            }
            return Collections.unmodifiableList(items);
        } catch (SQLException se) {
            logger.log(Level.SEVERE, "Finding items by category failed", se);
            throw new SlevnikException("Finding items by category failed", se);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException se2) {
                logger.log(Level.SEVERE, "Closing connection failed", se2);
                throw new SlevnikException("Closing connection failed", se2);
            }
        }
    }

    @Override
    public List<Item> findItemsByCityAndCategory(String city, Category category, String sortingParam, Boolean ascending) throws SlevnikException {
        if (city == null) {
            throw new IllegalArgumentException("City can't be null");
        }
        if (category == null) {
            throw new IllegalArgumentException("Category can't be null");
        }
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = pool.getConnection();
            if (ascending) {
                ps = con.prepareStatement("SELECT * FROM slevnik.items WHERE city = ? AND category_id = ? ORDER BY " + sortingParam + " ASC");
            } else {
                ps = con.prepareStatement("SELECT * FROM slevnik.items WHERE city = ? AND category_id = ? ORDER BY " + sortingParam + " DESC");
            }
            ps.setString(1, city);
            ps.setInt(2, category.getId());
            ResultSet rs = ps.executeQuery();
            List<Item> items = new ArrayList<Item>();
            while (rs.next()) {
                Item item = new ItemImpl();
                item.setId(rs.getInt("id"));
                item.setTitle(rs.getString("title"));
                item.setUrl(rs.getString("url"));
                item.setCity(rs.getString("city"));
                item.setFinalPrice(rs.getDouble("final_price"));
                item.setOriginalPrice(rs.getDouble("original_price"));
                item.setDiscount(rs.getDouble("discount"));
                item.setCustomers(rs.getInt("customers"));
                item.setDealStart(rs.getDate("dealstart"));
                item.setDealEnd(rs.getDate("dealend"));
                item.setCategoryId(rs.getInt("category_id"));
                items.add(item);
            }
            return Collections.unmodifiableList(items);
        } catch (SQLException se) {
            logger.log(Level.SEVERE, "Finding items by city and category failed", se);
            throw new SlevnikException("Finding items by city and category failed", se);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException se2) {
                logger.log(Level.SEVERE, "Closing connection failed", se2);
                throw new SlevnikException("Closing connection failed", se2);
            }
        }
    }

}
